﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using LibHuman;
namespace Human
{
	/// <summary>
	/// Summary description for frmThongKeTTGiaDinh.
	/// </summary>
	public class frmThongKeTTGiaDinh : System.Windows.Forms.Form
	{
		private AccessData mDB=new AccessData();
        Language lan = new Language();
		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.GroupBox groupBox2;
		private System.Windows.Forms.Label label6;
		private System.Windows.Forms.Label label7;
		private System.Windows.Forms.DataGrid dgrTKTinhTrangGiaDinh;
		private System.Windows.Forms.Button butKetThuc;
		private System.Windows.Forms.Label lbSoNamCoGD;
		private System.Windows.Forms.Label lbSoNuCoGD;
		private System.Windows.Forms.Label lbSoNamChuaCoGD;
		private System.Windows.Forms.Label lbSoNuChuaCoGD;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public frmThongKeTTGiaDinh()
		{
			InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(frmThongKeTTGiaDinh));
            this.dgrTKTinhTrangGiaDinh = new System.Windows.Forms.DataGrid();
            this.butKetThuc = new System.Windows.Forms.Button();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.lbSoNamCoGD = new System.Windows.Forms.Label();
            this.label2 = new System.Windows.Forms.Label();
            this.label1 = new System.Windows.Forms.Label();
            this.lbSoNuCoGD = new System.Windows.Forms.Label();
            this.groupBox2 = new System.Windows.Forms.GroupBox();
            this.lbSoNamChuaCoGD = new System.Windows.Forms.Label();
            this.label6 = new System.Windows.Forms.Label();
            this.label7 = new System.Windows.Forms.Label();
            this.lbSoNuChuaCoGD = new System.Windows.Forms.Label();
            ((System.ComponentModel.ISupportInitialize)(this.dgrTKTinhTrangGiaDinh)).BeginInit();
            this.groupBox1.SuspendLayout();
            this.groupBox2.SuspendLayout();
            this.SuspendLayout();
            // 
            // dgrTKTinhTrangGiaDinh
            // 
            this.dgrTKTinhTrangGiaDinh.AlternatingBackColor = System.Drawing.Color.Lavender;
            this.dgrTKTinhTrangGiaDinh.BackColor = System.Drawing.SystemColors.Control;
            this.dgrTKTinhTrangGiaDinh.BackgroundColor = System.Drawing.SystemColors.Control;
            this.dgrTKTinhTrangGiaDinh.BorderStyle = System.Windows.Forms.BorderStyle.None;
            this.dgrTKTinhTrangGiaDinh.CaptionBackColor = System.Drawing.SystemColors.Control;
            this.dgrTKTinhTrangGiaDinh.CaptionForeColor = System.Drawing.Color.MidnightBlue;
            this.dgrTKTinhTrangGiaDinh.CaptionText = "Thông Tin Gia Cảnh";
            this.dgrTKTinhTrangGiaDinh.DataMember = "";
            this.dgrTKTinhTrangGiaDinh.FlatMode = true;
            this.dgrTKTinhTrangGiaDinh.Font = new System.Drawing.Font("Tahoma", 8F);
            this.dgrTKTinhTrangGiaDinh.ForeColor = System.Drawing.Color.MidnightBlue;
            this.dgrTKTinhTrangGiaDinh.GridLineColor = System.Drawing.Color.Gainsboro;
            this.dgrTKTinhTrangGiaDinh.GridLineStyle = System.Windows.Forms.DataGridLineStyle.None;
            this.dgrTKTinhTrangGiaDinh.HeaderBackColor = System.Drawing.Color.MidnightBlue;
            this.dgrTKTinhTrangGiaDinh.HeaderFont = new System.Drawing.Font("Tahoma", 8F, System.Drawing.FontStyle.Bold);
            this.dgrTKTinhTrangGiaDinh.HeaderForeColor = System.Drawing.Color.WhiteSmoke;
            this.dgrTKTinhTrangGiaDinh.LinkColor = System.Drawing.Color.Teal;
            this.dgrTKTinhTrangGiaDinh.Location = new System.Drawing.Point(12, 9);
            this.dgrTKTinhTrangGiaDinh.Name = "dgrTKTinhTrangGiaDinh";
            this.dgrTKTinhTrangGiaDinh.ParentRowsBackColor = System.Drawing.Color.Gainsboro;
            this.dgrTKTinhTrangGiaDinh.ParentRowsForeColor = System.Drawing.Color.MidnightBlue;
            this.dgrTKTinhTrangGiaDinh.RowHeaderWidth = 10;
            this.dgrTKTinhTrangGiaDinh.SelectionBackColor = System.Drawing.Color.CadetBlue;
            this.dgrTKTinhTrangGiaDinh.SelectionForeColor = System.Drawing.Color.WhiteSmoke;
            this.dgrTKTinhTrangGiaDinh.Size = new System.Drawing.Size(427, 106);
            this.dgrTKTinhTrangGiaDinh.TabIndex = 0;
            // 
            // butKetThuc
            // 
            this.butKetThuc.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.butKetThuc.ForeColor = System.Drawing.Color.DarkBlue;
            this.butKetThuc.Image = ((System.Drawing.Image)(resources.GetObject("butKetThuc.Image")));
            this.butKetThuc.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.butKetThuc.Location = new System.Drawing.Point(172, 233);
            this.butKetThuc.Name = "butKetThuc";
            this.butKetThuc.Size = new System.Drawing.Size(105, 28);
            this.butKetThuc.TabIndex = 1;
            this.butKetThuc.Text = "     Kết thúc";
            this.butKetThuc.Click += new System.EventHandler(this.butKetThuc_Click);
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.lbSoNamCoGD);
            this.groupBox1.Controls.Add(this.label2);
            this.groupBox1.Controls.Add(this.label1);
            this.groupBox1.Controls.Add(this.lbSoNuCoGD);
            this.groupBox1.ForeColor = System.Drawing.Color.DarkBlue;
            this.groupBox1.Location = new System.Drawing.Point(74, 131);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(120, 96);
            this.groupBox1.TabIndex = 2;
            this.groupBox1.TabStop = false;
            this.groupBox1.Text = "Có gia đình";
            // 
            // lbSoNamCoGD
            // 
            this.lbSoNamCoGD.ForeColor = System.Drawing.Color.Red;
            this.lbSoNamCoGD.Location = new System.Drawing.Point(55, 23);
            this.lbSoNamCoGD.Name = "lbSoNamCoGD";
            this.lbSoNamCoGD.Size = new System.Drawing.Size(49, 23);
            this.lbSoNamCoGD.TabIndex = 5;
            this.lbSoNamCoGD.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
            // 
            // label2
            // 
            this.label2.ForeColor = System.Drawing.Color.Blue;
            this.label2.Location = new System.Drawing.Point(18, 68);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(27, 13);
            this.label2.TabIndex = 4;
            this.label2.Text = "Nữ :";
            this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // label1
            // 
            this.label1.ForeColor = System.Drawing.Color.Blue;
            this.label1.Location = new System.Drawing.Point(12, 28);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(35, 13);
            this.label1.TabIndex = 3;
            this.label1.Text = "Nam :";
            this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // lbSoNuCoGD
            // 
            this.lbSoNuCoGD.ForeColor = System.Drawing.Color.Red;
            this.lbSoNuCoGD.Location = new System.Drawing.Point(56, 64);
            this.lbSoNuCoGD.Name = "lbSoNuCoGD";
            this.lbSoNuCoGD.Size = new System.Drawing.Size(41, 23);
            this.lbSoNuCoGD.TabIndex = 6;
            this.lbSoNuCoGD.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
            // 
            // groupBox2
            // 
            this.groupBox2.Controls.Add(this.lbSoNamChuaCoGD);
            this.groupBox2.Controls.Add(this.label6);
            this.groupBox2.Controls.Add(this.label7);
            this.groupBox2.Controls.Add(this.lbSoNuChuaCoGD);
            this.groupBox2.ForeColor = System.Drawing.Color.DarkBlue;
            this.groupBox2.Location = new System.Drawing.Point(246, 131);
            this.groupBox2.Name = "groupBox2";
            this.groupBox2.Size = new System.Drawing.Size(120, 96);
            this.groupBox2.TabIndex = 3;
            this.groupBox2.TabStop = false;
            this.groupBox2.Text = "Chưa có gia đình";
            // 
            // lbSoNamChuaCoGD
            // 
            this.lbSoNamChuaCoGD.ForeColor = System.Drawing.Color.Red;
            this.lbSoNamChuaCoGD.Location = new System.Drawing.Point(55, 24);
            this.lbSoNamChuaCoGD.Name = "lbSoNamChuaCoGD";
            this.lbSoNamChuaCoGD.Size = new System.Drawing.Size(49, 23);
            this.lbSoNamChuaCoGD.TabIndex = 5;
            this.lbSoNamChuaCoGD.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
            // 
            // label6
            // 
            this.label6.ForeColor = System.Drawing.Color.Blue;
            this.label6.Location = new System.Drawing.Point(23, 68);
            this.label6.Name = "label6";
            this.label6.Size = new System.Drawing.Size(27, 13);
            this.label6.TabIndex = 4;
            this.label6.Text = "Nữ :";
            this.label6.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // label7
            // 
            this.label7.ForeColor = System.Drawing.Color.Blue;
            this.label7.Location = new System.Drawing.Point(15, 27);
            this.label7.Name = "label7";
            this.label7.Size = new System.Drawing.Size(35, 13);
            this.label7.TabIndex = 3;
            this.label7.Text = "Nam :";
            this.label7.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // lbSoNuChuaCoGD
            // 
            this.lbSoNuChuaCoGD.ForeColor = System.Drawing.Color.Red;
            this.lbSoNuChuaCoGD.Location = new System.Drawing.Point(55, 64);
            this.lbSoNuChuaCoGD.Name = "lbSoNuChuaCoGD";
            this.lbSoNuChuaCoGD.Size = new System.Drawing.Size(49, 23);
            this.lbSoNuChuaCoGD.TabIndex = 6;
            this.lbSoNuChuaCoGD.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
            // 
            // frmThongKeTTGiaDinh
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(451, 273);
            this.Controls.Add(this.groupBox2);
            this.Controls.Add(this.groupBox1);
            this.Controls.Add(this.butKetThuc);
            this.Controls.Add(this.dgrTKTinhTrangGiaDinh);
            this.ForeColor = System.Drawing.Color.Blue;
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.Name = "frmThongKeTTGiaDinh";
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Thống kê tình trạng gia đình";
            this.Load += new System.EventHandler(this.frmThongKeTTGiaDinh_Load);
            ((System.ComponentModel.ISupportInitialize)(this.dgrTKTinhTrangGiaDinh)).EndInit();
            this.groupBox1.ResumeLayout(false);
            this.groupBox2.ResumeLayout(false);
            this.ResumeLayout(false);

		}
		#endregion

		private void frmThongKeTTGiaDinh_Load(object sender, System.EventArgs e)
		{
			try
			{	
				DataSet ds=new DataSet();
				ds=f_Thongke_gd_phai("Nữ");
				DataRow[] r1=ds.Tables[0].Select("phai='Nữ'");
				lbSoNuCoGD.Text=r1[0]["cogd"].ToString();
				lbSoNuChuaCoGD.Text=r1[0]["chuagd"].ToString();

				DataSet ds1=new DataSet();
                ds1 = f_Thongke_gd_phai("Nam"); ;
				DataRow[] r=ds1.Tables[0].Select("phai='Nam'");
				lbSoNamCoGD.Text=r[0]["cogd"].ToString();
				lbSoNamChuaCoGD.Text=r[0]["chuagd"].ToString();				
			
				loadttgd();
			}
			catch{}
		}
        private DataSet f_Thongke_gd_phai(string m_phai)
        {
            string sql = "";
            sql="SELECT sum(case when hoan_canh=1 then 1 else 0 end) AS Cogd," ;
            sql += " sum(case when hoan_canh=0 then 1 else 0 end) AS Chuagd, l.phai";
            sql += " FROM " + mDB.user + ".llnv l left join " + mDB.user + ".hopdong hd on l.manv=hd.manv ";
            sql += " inner join "+mDB.user+".max_lan ml on hd.manv=ml.manv  ";
            sql+=" WHERE hd.ngaynghi is null and hd.lan=ml.lan and trim(phai)='"+m_phai+"' ";
            sql+= " GROUP BY phai";
            return mDB.get_data(sql);
        }
        
		private void butKetThuc_Click(object sender, System.EventArgs e)
		{
			this.Close();
		}
        public DataSet f_TongTinGC()
        {
            string sql = "";
            sql = "SELECT sum(case when hoan_canh=1 then 1 else 0 end) AS Cogd,";
            sql += " sum(case when hoan_canh=0 then 1 else 0 end) AS Chuagd,";
            sql += " sum(case when lietsi=1 then 1 else 0 end) as GdLs,";
            sql += " sum(case when lietsi=0 then 1 else 0 end) as GdKhongLs,";
            sql += " sum(case when thuongbinh=1 then 1 else 0 end) as gdtb,";
            sql += " sum(case when thuongbinh=0 then 1 else 0 end) as gdkhongtb";
            sql += " FROM " + mDB.user + ".llnv l left join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " inner join "+mDB.user+".max_lan ml on hd.manv=ml.manv  ";
            sql += " where hd.lan=ml.lan and hd.ngaynghi is null";
            return mDB.get_data(sql);
        }
		private void loadttgd()
		{
			try
			{
				DataSet ads=f_TongTinGC();
				
				dgrTKTinhTrangGiaDinh.DataSource=null;
				dgrTKTinhTrangGiaDinh.TableStyles.Clear();
				DataGridTableStyle ts = new DataGridTableStyle();
				ts.MappingName = ads.Tables[0].TableName;
					
				ts.AlternatingBackColor = Color.Beige;
				ts.BackColor = Color.GhostWhite;
				ts.ForeColor = Color.MidnightBlue;
				ts.GridLineColor = Color.RoyalBlue;
				ts.HeaderBackColor = Color.MidnightBlue;
				ts.HeaderForeColor = Color.Lavender;
				ts.SelectionBackColor = Color.Teal;
				ts.SelectionForeColor = Color.PaleGreen;
                ts.RowHeaderWidth = 10;
				
				DataGridTextBoxColumn TextCol1=new DataGridTextBoxColumn();
				TextCol1.MappingName = "Cogd";
				TextCol1.HeaderText = lan.Change_language_MessageText("Có Gia Đình");
				TextCol1.Width = 80;
				TextCol1.Alignment=HorizontalAlignment.Left;
				ts.GridColumnStyles.Add(TextCol1);
				dgrTKTinhTrangGiaDinh.TableStyles.Add(ts);
			
				DataGridTextBoxColumn TextCol2=new DataGridTextBoxColumn();
				TextCol2.MappingName = "chuagd";
				TextCol2.HeaderText = lan.Change_language_MessageText("Chưa Có Gia Đình");
				TextCol2.Width = 100;
				TextCol2.Alignment=HorizontalAlignment.Left;
				ts.GridColumnStyles.Add(TextCol2);
				dgrTKTinhTrangGiaDinh.TableStyles.Add(ts);
			
				DataGridTextBoxColumn TextCol3=new DataGridTextBoxColumn();
				TextCol3.MappingName = "Gdls";
				TextCol3.HeaderText = lan.Change_language_MessageText("Gia Đình Liệt Sĩ");
				TextCol3.Width = 110;
				TextCol3.Alignment=HorizontalAlignment.Left;
				ts.GridColumnStyles.Add(TextCol3);
				dgrTKTinhTrangGiaDinh.TableStyles.Add(ts);

                DataGridTextBoxColumn TextCol4 = new DataGridTextBoxColumn();
                TextCol4.MappingName = "Gdtb";
                TextCol4.HeaderText = lan.Change_language_MessageText("Gia Đình Thương Binh");
                TextCol4.Width = 113;
                TextCol4.Alignment = HorizontalAlignment.Left;
                ts.GridColumnStyles.Add(TextCol4);
                dgrTKTinhTrangGiaDinh.TableStyles.Add(ts);
			
			
				ts.DataGrid.DataSource=ads.Tables[0];

                CurrencyManager cm = (CurrencyManager)BindingContext[dgrTKTinhTrangGiaDinh.DataSource, dgrTKTinhTrangGiaDinh.DataMember];
                DataView dv = (DataView)cm.List;
                dv.AllowDelete = false;
                dv.AllowEdit = false;
                dv.AllowNew = false;

			}
			catch{}
		}
	}
}
